package org.NooLab.randomgraph.storage.db.h2;

/*
	http://www.h2database.com/html/jaqu.html#example_code
	http://www.cementframework.org/wiki/display/main/query-by-proxy
	http://stackoverflow.com/questions/154636/embedding-the-java-h2-database-programmatically

	org.h2.tools.Script.execute(String url, String user, String password, OutputStream out)
	http://h2database.com/javadoc/org/h2/tools/Script.html#execute_String_String_String_OutputStream


	for storing serialized objects (uses a byte-array internally): 
	
	PreparedStatement.setObject to store values.
	Mapped to java.lang.Object (or any subclass).
	
	
	
*/


// !!!!!!!!!!!!!!!!  import javax.*;


import java.sql.*;
import java.io.* ;


import org.h2.*;
import org.h2.jdbc.JdbcSQLException;

import org.NooLab.utilities.datetime.DateTimeValue;


// LOC 810

public class __obs_StreamDatabase {

	// this contains the create parameters. i.e. table definitions
	// and also description of entities
	// we then can use javassist to create the classes on the fly as instances of abstract classes
	
	// run script on connect
	// String url = "jdbc:h2:mem;INIT=RUNSCRIPT FROM '~/create.sql'\\;RUNSCRIPT FROM '~/populate.sql'"
	
	String configFile = "h2.db-config.xml";
	
	Connection connection;
	String user = "sa";
	String password = "" ;
	
	Class parent;
	String databaseFile="";
	String databaseName = "";
	
	DateTimeValue  datetimeValue ;
	
	MetaData metadata;
	

	public __obs_StreamDatabase( Class caller){
		parent = caller;
		datetimeValue = new DateTimeValue(14,1);
		
	}
 
	public void openConsole(){
		// pc only
		// execute: D:\dev\Eclipse\libs\database\h2\bin
	}
	/** there are two versions of the connect method<br/>
	 * this one here uses only the name of the database;<br/>
	 * The data base file will be stored in the user specific directory tree.<br/>
	 * 
	 * @param dbname name of the database
	 * @return effective storage path for the db file
	 * @throws FileNotFoundException
	 */
	public String connect( String dbname) throws FileNotFoundException{
	  
		return connect( dbname, "");
 
	}
	
	/**
	 * there are two versions of the connect method<br/>
	 * this one here expects an explicitly given path where the file
	 * will be stored.<br/>
	 * If the path does not exist, it will be attempted to be created, if 
	 * the mkdirs() command fails, <br/>the method falls back to its sibling, which
	 * uses only the dbname
	 * 
	 * @param dbname name of the database
	 * @param filepath intended storage path for the db file
	 * @return effective storage path for the db file
	 * @throws FileNotFoundException
	 */
	public String connect( String dbname, String filepath) throws FileNotFoundException{
		String dbfile ="";
		int err=0;
		
		try{
			 
			filepath = getDBhomefile( filepath );
			 
			
			File dir = new File(filepath);
			if (dir.exists()==false){
				dir.mkdirs();
			}
			
			
			/*
			 * /datastream/"+ package+"/data/"
			 */
			String url = 	"jdbc:h2:nio:"+filepath+"/"+dbname ;
				        /* 
				            ";MODE=MYSQL"
		    				";FILE_LOCK=FS" +
		    				";PAGE_SIZE=1024" +
		    				";CACHE_SIZE=8192";
						    DB_CLOSE_DELAY=10
				        */
		
			Class.forName("org.h2.Driver");
	        
			connection = DriverManager.getConnection( url, user, password);
			        // add application code here
			
			// datastream.h2.db
			dbfile = filepath+"/"+dbname+".h2.db" ;
			
			File fil = new File(dbfile);
			if (fil.exists()){
				dbfile = fil.getAbsolutePath();
			}
			else{
				dbfile = "";
			}
			

			// CALL DATABASE_PATH();
			
			File dbfil= new File(dbfile);
			 
			
			if (dbfil.exists()==false){
				throw new FileNotFoundException("\nDatabase file not found (err="+err+")\n"+
											    "expected directory : " + filepath+"\n"+
											    "expected db file   : " + dbname+".h2.db\n");
			}

			System.out.println("database connection url is : "+ url) ;
			
		}catch(JdbcSQLException jx){
			System.out.println("Connecting to database <"+dbname+"> failed \n"+jx.getMessage() );
			String lockfile = filepath+"/"+dbname+".lock.db";
			File fil = new File(lockfile);
			fil.deleteOnExit();
			fil.delete();
			
			dbfile = "";
			
		}catch( SQLException sx){
			System.err.println ("Cannot connect to database server");
			dbfile = "";
			sx.printStackTrace();
			
		}catch( ClassNotFoundException e){
			dbfile = "";
			e.printStackTrace();
		} 
		
		
		databaseName = dbname; 
		
		databaseFile = dbfile;
		return dbfile;
	}


	public void disconnect(){
		
		try{
			
			if (connection != null){
				connection.commit();
				
				 
			 
				 
				try{
					Thread.currentThread().sleep(100);
				}catch(Exception e){
					
				}
				connection.close();
				try{
					Thread.currentThread().sleep(100);
				}catch(Exception e){
					
				}
			}
			
		}catch( SQLException sx){
			sx.printStackTrace();
		}finally {
	         
	    }
		
	}

	public Connection getConnection(){
		
		return connection;
	}
	
	
	public void saveQueryas(String queryalias){
		
		
	}
	
	
	public void saveObject(){ // accepting a  ByteStream  from a serialized object
		
	}
	
	public void sampleCreateTable(){
		String tablename = "Employee" ;
		
		String sqlCmd = 	
					"CREATE TABLE "+tablename+"(  \n"+" "+
					"  id            int, \n"+
					"  first_name    VARCHAR(15),\n"+
					"  last_name     VARCHAR(15),\n"+
					"  start_date    DATE,\n"+
					"  end_date      DATE,\n"+
					"  salary        FLOAT(8,2),\n"+
					"  city          VARCHAR(10),\n"+
					"  description   VARCHAR(15)\n"+
					"  );  ";
			     
	}

	/**
	 * 
	 * This runs a select query (insert and update queries are treated separately)
	 * So far, it is not run capable to tun complicated joins across
	 * several tables
	 * 
	 * 
	 * @param table the name of the table in the opened database
	 * @param sql the sql command
	 * @param params the params for the sql command
	 */
	public void query( String table, String[] fields, String[] params){
		
		String field_list="", _condition ;
			
		PreparedStatement prep ;
		ResultSet resultset ;
		
		// SELECT * FROM KDATA 
		
		// variables from java program : (X INT=?) 
		
		// sql = "SELECT * FROM TABLE(X INT=?) T INNER JOIN TEST ON T.X=TEST.ID" ;
		// PreparedStatement prep = connection.prepareStatement( sql );
		// prep.setObject(1, new Object[] { "1", "2" });
		
		// SET @TOTAL = NULL;
		// SELECT X, SET(@TOTAL, IFNULL(@TOTAL, 1.) * X) F FROM SYSTEM_RANGE(1, 50);
	
		// exporting a csv file
		// CALL CSVWRITE('test.csv', 'SELECT * FROM TEST');
	
		// create fieldlist
		
		
		// create condition
		
		_condition = " WHERE " ;
		
		try{
			// 
			
			String sql_cmd =
				" SELECT "+ field_list +" FROM "+table+
				_condition
				 ;
			
			
			prep = connection.prepareStatement( sql_cmd );
			resultset = prep.executeQuery();
			
			
		}catch(SQLException sx){
			sx.printStackTrace();
		}
		
	
	}

	// TODO  better using a String[] and a map, this ensures appropriate matches
	public void insert(String table, String[] fields, String[] values){
		
		String field_list="", values_list="",v="";
		int i,k, fieldtype, _sensor_positions;
		
		
		try{
			
			for ( i=0;i<fields.length;i++){
				field_list = field_list + fields[i];
				
				if (i<fields.length-1){
					field_list = field_list + "," ;
					 
				}
			}
			field_list = "";
			
			_sensor_positions = values.length ;
			if ( fields.length < _sensor_positions){
				_sensor_positions = fields.length ;
			}
			
			for ( i=0;i< _sensor_positions;i++){
				v ="";
				// we need the type information !!
				// looking up type for 
				
				fieldtype = getNumTxtFieldType(table, fields[i]) ;
				if (fieldtype == 1){
					v = values[i] ;
				}
				if (fieldtype == 2){ // bool
					v = values[i] ;  // we expect that data are supplied as strings "true" or "false" 
				}
	
				if (fieldtype == 3){
					v = "'"+ values[i]+"'";	
				}
				if (fieldtype == 4){
					v = values[i] ;
				}
				
				if (v.length()>0){
					values_list = values_list + v;
					field_list = field_list + fields[i];
					
					if (i<values.length-1){
						values_list = values_list + "," ;
						field_list  = field_list  + "," ;
					}
				}
	
			} // i->
			
		}catch(Exception e){
			e.printStackTrace();
		}
		
		String sql_cmd =
						" INSERT INTO "+table+" "+
						" ("+ field_list +") "+
						" VALUES ("+ values_list +") ; "+
						"  ";
		sql_cmd = sql_cmd.replace(",)", ")");
		sql_cmd = sql_cmd.replace("(,", "(");
		try{
			if (sql_cmd.indexOf("()")<0){
				PreparedStatement prep = connection.prepareStatement( sql_cmd );
				prep.execute();
				connection.commit();
			}
		}catch(SQLException sx){
			sx.printStackTrace();
			
			// int recordid;
			// recordid = Integer.parseInt( values[0]);
			// indexvalueExists(table, "ID", recordid);
		}
		 // (id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
	     //               values (1,'Jason',    'Martin',  '19960725',  '20060725', 1234.56, 'Toronto',  'Programmer');
	}

	public void exportCSV(String tablename, String filename){
		String sql;
		Statement statement ;
		
		try{
			sql = "CALL CSVWRITE('"+filename+"', 'SELECT * FROM "+tablename+"');" ;
	
			statement = connection.createStatement();  
			statement.execute(sql);  
	    
			statement.close();
			
			 
		}catch(SQLException sx){
			 
			sx.printStackTrace();
		}
	}

	public void createMetaData(){
		
		// String getTables = "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE ='TABLE'; " ;
 
		metadata = new MetaData(connection, databaseName);
		
		/*
		 
		    1.  get all data tables in database
		    	SELECT  TABLE_NAME   FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE ='TABLE'
		 
		 	2.	get all columns in a selected table
		  	 	SELECT COLUMN_NAME   FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME ='KDATA';
		  	 	
		  	3.  get data type for any field
				SELECT DATA_TYPE   FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME ='KDATA' AND COLUMN_NAME ='NAME';
		  
		  	4.  get index field (property for each field)
		  		SELECT COLUMN_NAME  FROM INFORMATION_SCHEMA.INDEXES where TABLE_NAME ='KDATA'
		  		
		 */
		
	}
	
	
	private int getNumTxtFieldType( String table, String field){
		int rVal=-1;
		int tmpval = 0;
		// we loaded all meta info while opening
		DocoDbTable ptable ;
		DBColumn col;
		
		col = this.metadata.getbyNames(table,field) ;
	
		if (col!=null){
			tmpval = col.datatype;
			
			if (col.isNumerical()>=0){
				rVal=1;
			}
			if (col.isBool()>=0){
				rVal=2;
			}
			if (col.isTextual()>=0){
				rVal=3;
			}
			if (col.isTime()>=0){
				rVal=4;
			}
		}
		
		return rVal;
	}

	public void clearAllTables(){
		
		int n ;
		MetaData metadata;
		String sql;
		PreparedStatement prep;
		
		try{
			if (connection.isClosed()){ return; }
			n = getTableCount();
			metadata = getcurrentMetaData();
			
			for( DocoDbTable t : metadata.tables){
				
				sql = "DROP TABLE IF EXISTS " + t.name+ ";" ;
				
				prep = connection.prepareStatement( sql );
				prep.execute();
			}
			
		}catch(SQLException sx){
			sx.printStackTrace();
		}
	}
	
	public void delete(){
		try{
			
			clearAllTables();
			
			if (connection != null){
				connection.close();
			}
			
			File fil = new File(databaseFile) ;
			
			if(fil.exists()){
				fil.delete();
			}
			
		}catch( SQLException sx){
			sx.printStackTrace();
		}
		// now delete the db file ...
		
	}

	/**
	 * 
	 * @param keep <ul>
	 *             0=overwrite the table if it already exists;<br/>
	 *             1=using the provided name of the table as a basic name, <br/>
	 *             stuffing it with a serial enumeration "00000" and the  <br/>
	 *             current date-time;<br/>
	 *             2=append, if the column headers match, else fall back to keep=1;<br/>
	 *             </ul><br/>
	 * @param tablename <ul>the basic name of the table to be created;<br/>
	 * if keep=1, the tablename will be changed
	 * </ul>
	 * @param columnheaders the column names for the table to be created
	 * @return the actual name of the opened table, which will be different<br/>
	 * from the requested one, if keep=1;<br/> 
	 */
	public String createDataTable( int keep , String tablename, String[] columnheaders){
		/*
		 
		  	DROP TABLE IF EXISTS Data;
			CREATE TABLE Data(ID INT PRIMARY KEY, timedist VARCHAR(255), A VARCHAR(255) , B VARCHAR(255)   );
			INSERT INTO Data VALUES( 11, '964528128' , '29.86'  , ' 226.8');
			SELECT * FROM Data ORDER BY ID;
		 */
		PreparedStatement prep;
		ResultSet resultset ;
		Statement statement;
		String sql, dropp="";
		String dtstr="";
		String name_of_createdtable="",hs1;
		boolean result;
		
		tablename = tablename.toUpperCase();
		
		if (keep==0){
			dropp = "DROP TABLE IF EXISTS "+tablename+"; ";
		}
		if (keep==1){ // use provided tablename as a basis
			// change tablename
			
			int n = getTableCount(tablename);
			// possible name: DATA_xyz_0001_2342342
			
			 
			dtstr = datetimeValue.get(); // according to defined format
			
			hs1 = String.format("%05d", n);
			
			tablename = tablename + "_"+hs1+"_" + dtstr;
			
		}	
	
		
		sql = dropp +
			  "CREATE TABLE "+tablename+" (ID INT PRIMARY KEY, timedist VARCHAR(255), A VARCHAR(255) , B VARCHAR(255)   ); "+
			  "";
			  // "INSERT INTO "+tablename+" VALUES( 1, '1' , '29.86'  , ' 226.8'); " ;
			  // "SELECT * FROM Data ORDER BY ID;"; " ;
		
		try{
			// prep = connection.prepareStatement( sql );
			// prep.execute();
			
			if (keep<2){ // append mode 
				statement = connection.createStatement();  
				statement.execute(sql);  
	        
				statement.close();
			}
			
	        name_of_createdtable = tablename;
	        
		}catch(SQLException sx){
			name_of_createdtable = "";
			sx.printStackTrace();
		}
		
		metadata = new MetaData(connection, databaseName);
		
		return name_of_createdtable;
	}

	public DocoDbTable getTable(String tablename){
		DocoDbTable table=null;
		 
		int ix=-1;
		
		int tn = metadata.tables.size();
		  
		for (DocoDbTable t : metadata.tables){
			ix++;
			if (tablename.contentEquals(t.name)){
				table = t;
				break;
			}
		}
	  
		return table;
	}
	
	public String[] getTables(){
		String[] tablenames = null;
		int ix=-1;
		
		int tn = metadata.tables.size();
		tablenames = new String[tn];
		
		for (DocoDbTable t : metadata.tables){
			ix++;
			tablenames[ix] = t.name;
		}
		
		return tablenames;
	}
	
	public int getTableCount(){
		return getTableCount("*") ;
	}
	
	public int getTableCount( String namesnip){
		int z,rVal=-1;
		ResultSet resultset;
		String sql,approx;
		
		// SELECT  TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE ='TABLE' AND TABLE_NAME LIKE 'DATA%'
		
		approx = " AND TABLE_NAME LIKE '%"+namesnip+"%';" ;
				 
		if (namesnip.contentEquals("*")){
			approx="";
		}
		else{
			if (namesnip.contains("*")){
				namesnip = namesnip.replace("*","%");
				approx = " AND TABLE_NAME LIKE '"+namesnip+"';" ;
			}	
			else{
				if (namesnip.length()>0){
					
				}
			}
		}
			
		sql = "SELECT  TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE ='TABLE' " +approx;
		
		try{
			PreparedStatement prep = connection.prepareStatement( sql );
			resultset = prep.executeQuery();
			 
			z=0;
			while (resultset.next()){
				z++;
			}
			rVal = z;
			resultset.close();

		} catch( SQLException sx ){
			sx.printStackTrace();
		} finally{
			 
		}
		
		return rVal;
	}
	
	private boolean indexvalueExists(String table, String id_field, int indexvalue){
		String sql;
		boolean rb=false;
		ResultSet resultset;
		int r=-1,z=0;
		
		sql = "SELECT "+id_field+" FROM "+table+ " WHERE "+id_field+"="+indexvalue +";";
		
		try{
			if (connection.isClosed()){
				return false;
			}
			
			PreparedStatement prep = connection.prepareStatement( sql );
			resultset = prep.executeQuery();
			
			while (resultset.next()){
				r = resultset.getInt(id_field);
				z++;
			}
			if (z>0){
				rb=true;
			}
		}catch(SQLException sx){
			sx.printStackTrace();
		}
		return rb;
	}

	public void defineProcecdure( String cmdString, String name){
		
		cmdString = "delimiter $$ \n"+ cmdString;
		 
		// remove it:  DROP PROCEDURE myProc;
		
		// we need a procedure class for serializing the object, which then will contain
		// the cmdStr calling params, rights, result statistics ...
	}

	// ==============================================================================================
	/** the data array has to match the table definition !!! 
	* if the data array is 1 position too long, it is assumed, that the first position should
	* be removed (only if it contains an integer)
	*/
	public int write( String tablename, int recordid, String[] dataarr) {
		int recordcount=-1, newlen ;
		String[] fields = null ;
		DocoDbTable table;
		 
		
		if (dataarr==null){
			return recordcount;
		}
		
		fields = metadata.getColumnNames(tablename) ;
		/*
		newlen = dataarr.length-1;
		String[] newArray=new String[newlen];
		System.arraycopy(dataarr,1,newArray,0,newlen);
		 */
		
		/*
		 *  UPDATE "table_name"
			SET column_1 = [value1], column_2 = [value2]
			WHERE {condition}
		 */
		
		// get "ID" from metadata ...
		if (indexvalueExists(tablename, "ID", recordid)== true){
			
		}
		else{
			if (fields!=null){
				insert( tablename, fields, dataarr) ;
			}
		}
		return recordcount;
	}

	
	
	public  void setMetaData( MetaData meta_data){
		metadata = meta_data;
	}

	public MetaData getcurrentMetaData() {
		return metadata;
	}

	public int setConfigFile(String configfile) throws FileNotFoundException{
		configFile = configfile ;
		int rVal = -1;
		String rValStr ="";
		
		File fil = new File(configFile);
		
		String parentdir = fil.getParent();
		if ((parentdir==null) || (parentdir.length()<=1)){
			parentdir = getDBhomefile( "");
			
			configFile = parentdir+"/"+configFile ;
			configFile = configFile.replace("//", "/");
			
			fil = new File(configFile);
			configFile = fil.getAbsolutePath();
			rVal=-2;
			rValStr ="-2";
		}
		
		rValStr ="CfgFile not found: "+configFile;
		if (fil.exists()==true){
			rVal=0;
			
		}
		if (rVal<0){
			System.out.println(rValStr);
			throw new FileNotFoundException("Config File Error ("+rVal+"): "+rValStr);
		}
		rValStr ="";
		return rVal;
	}
	
	
	
	// ==============================================================================================
	
	private String getDBhomefile( String basepath){
		String filename="",filepath, user_home;
			
		File dir = new File(basepath);
		if (dir.exists()==false){
			dir.mkdirs();
			if (dir.exists()==false){
				basepath ="";
			}
			else{
				basepath = dir.getAbsolutePath();
			}
		}
		
		
		if (basepath.length()==0){
			user_home= System.getProperty("user.home");
		}
		else{
			user_home = basepath;
		}
		
		user_home = user_home + "/data/" + parent.getName();
		
		filepath = user_home;
		dir = new File(filepath);
		if (dir.exists()==false){
			dir.mkdirs();
			
		}
		filepath = dir.getAbsolutePath() ;
		
		return filepath;
	}

	public String sampleprocedure(){
		 String cmdStr="" ;
		 
		 
		 cmdStr = 	"CREATE PROCEDURE myProc(normal_price NUMERIC(8,2),OUT discount_price NUMERIC(8,2)) \n"+
					"NO SQL    \n"+
					"BEGIN    \n"+
					"IF (normal_price>500) THEN    \n"+
					"  SET discount_price=normal_price*.8;    \n"+
					"ELSEIF (normal_price>100) THEN    \n"+
					"  SET discount_price=normal_price*.9;    \n"+
					"ELSE    \n"+
					"  SET discount_price=normal_price;   \n"+
					"END IF;   \n"+
					"select discount_price;    \n"+
					"END$$    \n"+
					"    \n"+
		 			"";
	      return cmdStr;
	}
	
	
	
}


